Generated code - Sorting, SelfServicing
Preface
This section discusses briefly the server-side sorting capabilities of the
LLBLGen Pro runtime framework. The sorting discussed below is executed as an
ORDER BY clause in the generated query. If you want to sort the data in an
already fetched collection class, you should use an
EntityView<T>
on the collection and sort that instead.
Sorting
Sorting is the ability to order data in one or more fields ascending (A ->
Z) or descending (Z -< A). You do this by constructing a SortExpression with
one or more SortClauses. SortClauses are simple definitions which contain
information about which field to sort and in which direction
(ascending/descending).
When you're using the native language filter
construction method to formulate filters, it's convenient to also use this
for SortExpressions and sortclauses. Below is an example which creates a
SortExpression to sort on Customer.Country Ascending and
Customer.CompanyName descending. Both methods are shown (regular and native
language).
// C#
SortExpression sorter = new SortExpression(CustomerFields.Country | SortOperator.Ascending) &
(CustomerFields.CompanyName | SortOperator.Descending);
' VB.NET
Dim sorter As New SortExpression(CustomerFields.Country Or SortOperator.Ascending) And _
(CustomerFields.CompanyName Or SortOperator.Descending)
 Note:
|
|
If you specify a sort clause or a set of sortclauses and a RelationCollection (which is almost always the case with a typed list)
while you also specify that duplicates are not allowed, be sure the sort clauses are referring to fields in the resultset, otherwise
the database can't obey the sort rule and will throw an exception, since all fields mentioned in an ORDER BY clause (which
is the result of a sort clause) have to be in the resultset when a DISTINCT statement (the result of the specification that
no duplicate rows have to be retrieved) is included. When you want to sort on a field which has an aggregate function or an expression applied to it,
be sure to specify the aggregate function or expression object to the field in the SortClause as well, with the same alias.
|
Case-insensitive sorting
On case-sensitive databases (default Oracle installations, Firebird etc.) it can be you want to sort alpha-numeric data case-insensitive. To achieve that, you
should set the
SortClause object's property
CaseSensitiveCollation to
true, identical to the FieldLikePredicate system for case-insensitive
filtering. Setting this property to true will make the query generator emit UPPER() around the field, thus UPPER(fieldname), or equivalent function for UPPER() on the
particular database. Example, which sorts case insensitive on companyname:
// C#
SortExpression sorter = new SortExpression();
sorter.Add(CustomerFields.Country | SortOperator.Ascending);
sorter.Add(CustomerFields.CompanyName | SortOperator.Descending);
sorter[1].CaseSensitiveCollation=true;
' VB.NET
Dim sorter As New SortExpression()
sorter.Add(New SortClause(CustomerFields.Country, SortOperator.Ascending))
sorter.Add(New SortClause(CustomerFields.CompanyName, SortOperator.Descending))
sorter(1).CaseSensitiveCollation=True
Sorting on an expression
When a field with an expression is placed in a sort clause, it gives a
dilemma: is this a field which is already in the select list, so an alias
has to be emitted or is this an expression to use as the Order By clause? By
default, LLBLGen Pro Runtime Framework will choose the former, as it's the
most common, and will emit the alias set on the field instead of the
expression. To sort on an expression however, the property
sortclause.EmitAliasForExpressionAggregateField
has to be set to true (default: false).
Example, using a
DbFunctionCall as an expression to sort on.
// C#
OrderCollection orders = new OrderCollection();
// prepare the function call and the field to sort on
IExpression datePart = new DbFunctionCall("CAST({0} AS DATE)", new object[] { OrdersFields.OrderDate });
IEntityField datePartField = OrdersFields.OrderDate.SetExpression(datePart);
// prepare the sorter
ISortClause datePartSortClause = new SortClause(datePartField, SortOperator.Descending);
datePartSortClause.EmitAliasForExpressionAggregateField = false;
ISortExpression sorter = new SortExpression(datePartSortClause);
// fetch
orders.GetMulti(null, 0, sorter);
' VB.NET
Dim orders As OrderCollection()
' prepare the function call and the field to sort on
Dim parameters As New Object(0)
parameters(0) = OrdersFields.OrderDate
Dim datePart As New DbFunctionCall("CAST({0} AS DATE)", parameters)
Dim datePartField As OrdersFields.OrderDate.SetExpression(datePart)
' prepare the sorter
Dim datePartSortClause As New SortClause(datePartField, SortOperator.Descending)
datePartSortClause.EmitAliasForExpressionAggregateField = False
Dim sorter As New SortExpression(datePartSortClause)
' fetch
orders.GetMulti(Nothing, 0, sorter)
